package taobao

import org.apache.spark.SparkContext
import org.apache.spark.sql.SparkSession

/**
  * @ Auther: o2o-rd-0008
  * @ Date:   2020/11/17 16:36
  * @ Param:  ${PARAM}
  * @ Description: 
  */
object TaobaoDeal {
  def main(args: Array[String]): Unit = {
    val spark: SparkSession = SparkSession.builder()
      .config("spark.debug.maxToStringFields", "2000")
      .config("spark.serializer", "org.apache.spark.serializer.KryoSerializer")
//        .master("local[*]")
      .getOrCreate()


    val sc: SparkContext = spark.sparkContext
    sc.hadoopConfiguration.set("fs.s3a.access.key", "GAO7EO9FWKPJ8WFCQDME")
    sc.hadoopConfiguration.set("fs.s3a.secret.key", "LZ0xaHBSYKHaJ9ECDbX9f7zin79UZkXfGoNapRPL")
    sc.hadoopConfiguration.set("fs.s3a.endpoint", "https://obs.cn-north-1.myhuaweicloud.com")
    sc.setLogLevel("WARN")

    /*val sourceDF: DataFrame = spark.read.orc("s3a://o2o-dataproces-group/zsc/product/taobao/2020/10/taobaoFinalData/")
                                .drop("firstCategoryId").drop("secondCategoryId").drop("thirdCategoryId").drop("fourthCategoryId")

    val cateDF: DataFrame = spark.read.option("header",true).csv("s3a://o2o-dataproces-group/zsc/product/taobao/2020/10/big_cate/").dropDuplicates()
      .withColumnRenamed("platform_category_id","categoryId")
      .withColumnRenamed("o2o_firstcategoryid","firstCategoryId")
      .withColumnRenamed("o2o_secondcategoryid","secondCategoryId")
      .withColumnRenamed("o2o_threecategoryid","thirdCategoryId")
      .withColumnRenamed("o2o_fourcategoryid","fourthCategoryId")

    val result: DataFrame = sourceDF.join(cateDF,Seq("categoryId"))

    result.write.orc("s3a://o2o-dataproces-group/zsc/product/taobao/2020/10/gy_big_cate_result_new/")*/

//    spark.read.orc("s3a://o2o-dataproces-group/zsc/product/taobao/2020/10/gy_big_cate_result_new/").registerTempTable("tab")
    spark.read.orc("s3a://o2o-dataproces-group/zsc/product/taobao/2020/10/taobaoFinalData/").cache().registerTempTable("stab")
//    spark.read.option("header",true).csv("s3a://o2o-dataproces-group/zsc/taobao_new_cate/all_cate/").registerTempTable("catetab")
//    val frame: DataFrame = spark.read.orc("s3a://o2o-dataproces-group/zsc/product/taobao/2020/10/taobaoFinalData/")

//    frame.groupBy("").mean().distinct().agg("id" -> "max", "c4" -> "sum")


    //算当前字段中，每个值出现概率大于0.3的值有哪些
//    frame.stat.freqItems(Seq(""),0.3)

    //行转列
//    frame.explode("c1","c1_"){x:String => x.split(",")}

    spark.sql(
      """
        |select
        |fourthCategoryId,
        |sum(salesAmount),
        |sum(sellCount)
        |from
        |stab
        |where categoryId='121468012'
        |group by fourthCategoryId
      """.stripMargin).show(false)

    spark.sql(
      """
        |select
        |fourthCategoryId,
        |sum(salesAmount),
        |sum(sellCount)
        |from
        |stab
        |where categoryId='50007006'
        |group by fourthCategoryId
      """.stripMargin).show(false)

    spark.sql(
      """
        |select
        |fourthCategoryId,
        |sum(salesAmount),
        |sum(sellCount)
        |from
        |stab
        |where categoryId='121410013'
        |group by fourthCategoryId
      """.stripMargin).show(false)

    spark.sql(
      """
        |select
        |fourthCategoryId,
        |sum(salesAmount),
        |sum(sellCount)
        |from
        |stab
        |where categoryId='50011991'
        |group by fourthCategoryId
      """.stripMargin).show(false)

    /*spark.sql(
      """
        |select
        |t4.`fourthname`,
        |t3.*,
        |t5.`fourthname` as fourthnameold
        |from
        |(select
        |t1.*,
        |"" as tmp,
        |t2.*,
        |t1.salesnew-t2.salesold as sales_jian,
        |t1.sellnew-t2.sellold as sell_jian
        |from
        |(select
        |categoryId as categoryIdnew,
        |--categoryName,
        |firstCategoryId as firstCategoryIdnew,
        |secondCategoryId as secondCategoryIdnew,
        |thirdCategoryId as thirdCategoryIdnew,
        |fourthCategoryId as fourthCategoryIdnew,
        |sum(salesAmount) salesnew,
        |sum(sellCount) sellnew
        |from
        |stab
        |where thirdcategoryId in('100200402','100190105','100190107','100190203','100190601','100190108','100190106',
        |'100220201','100190801','100190204','100190202','100190101','100190201','100200202',
        |'100190102','100200401','100251002','100190106','100140103','100210203','100160703',
        |'100190205','100150103','100190603','100190605','100200201') or fourthCategoryId in ('10021020701','10019010301','10019010302')
        |or secondCategoryId in ('1001903','1001904')
        |group by categoryId,firstCategoryId,secondCategoryId,thirdCategoryId,fourthCategoryId) t1
        |left join
        |(select
        |categoryId as categoryIdold,
        |--categoryName,
        |firstCategoryId as firstCategoryIdold,
        |secondCategoryId as secondCategoryIdold,
        |thirdCategoryId as thirdCategoryIdold,
        |fourthCategoryId as fourthCategoryIdold,
        |sum(salesAmount) salesold,
        |sum(sellCount) sellold
        |from
        |tab
        |--where thirdcategoryId in('100200402','100190105','100190107','100190203','100190601','100190108','100190106',
        |--'100220201','100190801','100190204','100190202','100190101','100190201','100200202',
        |--'100190102','100200401','100251002','100190106','100140103','100210203','100160703',
        |--'100190205','100150103','100190603','100190605','100200201') or fourthCategoryId in ('10021020701','10019010301','10019010302')
        |--or secondCategoryId in ('1001903','1001904')
        |group by categoryId,firstCategoryId,secondCategoryId,thirdCategoryId,fourthCategoryId) t2
        |on t1.categoryIdnew=t2.categoryIdold
        |) t3
        |left join
        |(
        |select distinct `lastcategoryid `,`fourthname` from catetab
        |) t4
        |on t3.categoryIdnew=t4.`lastcategoryid `
        |left join
        |(
        |select distinct `lastcategoryid `,`fourthname` from catetab
        |) t5
        |on t3.categoryIdold=t5.`lastcategoryid `
      """.stripMargin).repartition(1).write.option("header",true).csv("s3a://o2o-dataproces-group/zsc/taobao_new_cate/7/")*/

    /*spark.sql(
      """
        |select
        |t1.thirdCategoryId,
        |t2.thirdCategoryId,
        |t1.ct,
        |t2.ct,
        |t1.sell,
        |t2.sell,
        |t1.sales,
        |t2.sales
        |from
        |(select
        |thirdCategoryId,
        |count(1) ct,
        |sum(sellCount) sell,
        |sum(salesAmount) sales
        |from
        |tab
        |where firstCategoryId='10019'
        |group by thirdCategoryId) t1
        |left join
        |(select
        |thirdCategoryId,
        |count(1) ct,
        |sum(sellCount) sell,
        |sum(salesAmount) sales
        |from
        |stab
        |where firstCategoryId='10019'
        |group by thirdCategoryId) t2
        |on t1.thirdCategoryId=t2.thirdCategoryId
      """.stripMargin).show(false)*/

    /*println("服装")

    spark.sql(
      """
        |select
        |t1.categoryId,
        |t1.categoryName
        |from
        |(select
        |categoryId,
        |categoryName
        |from
        |stab
        |where firstCategoryId='10011'
        |group by categoryId,categoryName) t1
        |left join
        |(select
        |categoryId,
        |categoryName
        |from
        |tab
        |where firstCategoryId='10011'
        |group by categoryId,categoryName) t2
        |on t1.categoryId=t2.categoryId
        |where t2.categoryId is null
      """.stripMargin).show(false)
*/

    /*spark.sql(
      """
        |select
        |t1.thirdCategoryId,
        |t2.thirdCategoryId,
        |t1.ct,
        |t2.ct,
        |t1.sell,
        |t2.sell,
        |t1.sales,
        |t2.sales
        |from
        |(select
        |thirdCategoryId,
        |count(1) ct,
        |sum(sellCount) sell,
        |sum(salesAmount) sales
        |from
        |tab
        |where firstCategoryId='10011'
        |group by thirdCategoryId) t1
        |left join
        |(select
        |thirdCategoryId,
        |count(1) ct,
        |sum(sellCount) sell,
        |sum(salesAmount) sales
        |from
        |stab
        |where firstCategoryId='10011'
        |group by thirdCategoryId) t2
        |on t1.thirdCategoryId=t2.thirdCategoryId
      """.stripMargin).show(false)*/

  }
}
